Re: DataDirect Driver, ExecDirect and UTF-8

--_000_C625D8A0B8ksellgreenplumcom_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,

I wasn't a member of the mailing list when I sent this, so I'm not sure it =
actually made it out there.
I apologize if this is a duplicate.

.....Ken


On 5/4/09 11:49 AM, "Ken Sell" wrote:

Hi,

I'm a the new connectivity developer at GreenPlum. GreenPlum makes a data w=
arehouse DBMS based on PostgreSQL.
I'm working on a problem where a user is attempting to insert a non-ASCII U=
TF-8 values (i.e. An umlaut over an 'o').
The test does an insert via an SQLExecDirectW into a table. The text looks=
like this:

"insert into t1 values ('ö')"

I've built and debugged the postgreSQL driver (version 8.02.0500). It looks=
like the text makes it through the
Driver Manager (i.e. DataDirect) ok. I see the correct value in SQLExecDire=
ctW in odbcapiw.c, but I
also see the code in SQLExecDirectW call ucs2_to_utf8. ucs2_to_utf8 tries t=
o interpret the value as
UCS2, but the value is UTF-8. The value is corrupted by ucs2_to_utf8.

I also attempted to call SQLExecDirect (i.e. no W), but the DataDirect driv=
er manager tries to convert the
umlaut value to ASCII and calls SQLExecDirectW instead.

Can someone elaborate on the driver's correct behavior in this situation? I=
f the database is UTF-8 and
The application is UTF-8, should the driver handle this? Does the applicati=
on (or driver manager) have
to convert the string to UCS2 first?

Thanks,

.....Ken

--_000_C625D8A0B8ksellgreenplumcom_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable






11pt'>Hi,



I wasn’t a member of the mailing list when I sent this, so I’m =
not sure it actually made it out there.

I apologize if this is a duplicate.



.....Ken





On 5/4/09 11:49 AM, "Ken Sell" < ">ksell@greenplum.com> wrote:

>Hi,



I’m a the new connectivity developer at GreenPlum. GreenPlum makes a =
data warehouse DBMS based on PostgreSQL.

I’m working on a problem where a user is attempting to insert a non-A=
SCII UTF-8 values (i.e. An umlaut over an ‘o’).

The test does an insert via an SQLExecDirectW into a table.  The text =
looks like this:



  "insert into t1 values ('ö')"



I’ve built and debugged the postgreSQL driver (version 8.02.0500). It=
looks like the text makes it through the

Driver Manager (i.e. DataDirect) ok. I see the correct value in SQLExecDire=
ctW in odbcapiw.c, but I

also see the code in SQLExecDirectW call ucs2_to_utf8. ucs2_to_utf8 tries t=
o interpret the value as

UCS2, but the value is UTF-8. The value is corrupted by ucs2_to_utf8.



I also attempted to call SQLExecDirect (i.e. no W), but the DataDirect driv=
er manager tries to convert the

umlaut value to ASCII and calls SQLExecDirectW instead.



Can someone elaborate on the driver’s correct behavior in this situat=
ion? If the database is UTF-8 and

The application is UTF-8, should the driver handle this? Does the applicati=
on (or driver manager) have

to convert the string to UCS2 first?

 

Thanks,



.....Ken

Re: DataDirect Driver, ExecDirect and UTF-8

Ken Sell wrote:
> Hi,
>=20
> I wasn=92t a member of the mailing list when I sent this, so I=92m not =
sure=20
> it actually made it out there.
> I apologize if this is a duplicate.
>=20
> ....Ken
>=20
>=20
> On 5/4/09 11:49 AM, "Ken Sell" wrote:
>=20
> Hi,
>=20
> I=92m a the new connectivity developer at GreenPlum. GreenPlum make=
s a
> data warehouse DBMS based on PostgreSQL.
> I=92m working on a problem where a user is attempting to insert a
> non-ASCII UTF-8 values (i.e. An umlaut over an =91o=92).
> The test does an insert via an SQLExecDirectW into a table. The
> text looks like this:
>=20
> "insert into t1 values ('ö')"
>=20
> I=92ve built and debugged the postgreSQL driver (version 8.02.0500)=
..
> It looks like the text makes it through the
> Driver Manager (i.e. DataDirect) ok. I see the correct value in
> SQLExecDirectW in odbcapiw.c, but I
> also see the code in SQLExecDirectW call ucs2_to_utf8. ucs2_to_utf8
> tries to interpret the value as
> UCS2, but the value is UTF-8. The value is corrupted by ucs2_to_utf=
8.

Psqlodbc Unicode driver uses UTF-16 encoding and your application uses
UTF-8 encoding. Isn't the URL
http://media.datadirect.com/download/docs/odbc/allodbc/refer ence/unicode6=
..html
related to your problem ?

regards,
Hiroshi Inoue

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: DataDirect Driver, ExecDirect and UTF-8

--_000_C631E68E133ksellgreenplumcom_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,

Thanks very much for the information. I think I have it figured out.

Here is my understanding. Feel free to correct me if I have gotten somethin=
g wrong.
In order for everything to work correctly with a UTF-8 application, a DataD=
irect driver manager, and psqlodbc you must do the following.

- Use the header files (e.g. sql.h) from the DataDirect driver manager ins=
tallation.

- Compile the driver with the SQLWCHARSHORT define (e.g. #define SQLWCHARS=
HORT=3D1).
This will cause SQLWCHAR to be defined as an unsigned short.

- Compile the application with the SQLWCHARSHORT define (e.g. #define SQL=
WCHARSHORT=3D1).
This will cause SQLWCHAR to be defined as an unsigned short.

- Set the DriverUnicodeType data source attribute to 1 (i.e. DriverUnicod=
eType=3D1 in your odbc.ini file).
This tells the driver manager that your driver is expecting UTF-16 encod=
ing for all SQLWCHAR
parameters.

- If your database encoding is UTF-8, the descriptor for character columns=
will be a wide type.
For example, a char(1) column will be SQL_WCHAR.

- If you do something with a sql statement (e.g. Do a SQLExecDirectW("inser=
t into T1 values ('a')),
the driver manager (DataDirect) will convert the string from UTF-8 to UTF=
-16, pass it to the
the driver as UTF-16. The driver will then translate it back to UTF-8. Wh=
en the driver is
unicode (i.e your compiled psqlodbc with as a unicode driver), then this =
translation
in the driver manager happens weather or not you use a wide function. The=
driver manager
converts both SQLCHAR and SQLWCHAR parameters to UTF-16. It also changes =
all calls to
non-wide functions to calls to wide functions to the driver (e.g. SQLExec=
Direct gets changed
to SQLExecDirectW). Note, SQLCHAR and SQLWCHAR strings are not translated=
from UTF-8
to UTF-16 in the same way. If the call is to a non-wide function, then th=
e translation assumes
that the string contains only ASCII characters. A non-ASCII character wil=
l probably get
translated incorrectly. A call to wide functions translates UTF-8 to UTF-=
16 correctly.

- When using parameters, you must be use the appropriate SQL and C type wh=
en describing
the parameters column values. If you say the C type is SQL_C_CHAR, the =
value is assumed
to be in UTF-8 by the driver for a database encoded in UTF-8. IF you sa=
y the C type is
SQL_C_WCHAR, I think the driver will assume the value is in UTF-16 and t=
ry to translate
it to UTF-8.

- When using column values, you must use the appropriate SQL C type. If yo=
u say the column C
type is SQL_C_WCHAR, then the returned value will be translated to UTF-1=
6 by the driver.
If you say the column C type is SQL_C_CHAR, then the value will be retur=
ned as UTF-8.

.....Ken


On 5/5/09 11:13 PM, "Hiroshi Inoue" wrote:

Ken Sell wrote:
> Hi,
>
> I wasn't a member of the mailing list when I sent this, so I'm not sure
> it actually made it out there.
> I apologize if this is a duplicate.
>
> ....Ken
>
>
> On 5/4/09 11:49 AM, "Ken Sell" wrote:
>
> Hi,
>
> I'm a the new connectivity developer at GreenPlum. GreenPlum makes a
> data warehouse DBMS based on PostgreSQL.
> I'm working on a problem where a user is attempting to insert a
> non-ASCII UTF-8 values (i.e. An umlaut over an 'o').
> The test does an insert via an SQLExecDirectW into a table. The
> text looks like this:
>
> "insert into t1 values ('ö')"
>
> I've built and debugged the postgreSQL driver (version 8.02.0500).
> It looks like the text makes it through the
> Driver Manager (i.e. DataDirect) ok. I see the correct value in
> SQLExecDirectW in odbcapiw.c, but I
> also see the code in SQLExecDirectW call ucs2_to_utf8. ucs2_to_utf8
> tries to interpret the value as
> UCS2, but the value is UTF-8. The value is corrupted by ucs2_to_utf8.

Psqlodbc Unicode driver uses UTF-16 encoding and your application uses
UTF-8 encoding. Isn't the URL
http://media.datadirect.com/download/docs/odbc/allodbc/refer ence/unicode6.h=
tml
related to your problem ?

regards,
Hiroshi Inoue


--_000_C631E68E133ksellgreenplumcom_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable






11pt'>Hi,



Thanks very much for the information. I think I have it figured out.



Here is my understanding. Feel free to correct me if I have gotten somethin=
g wrong.

In order for everything to work correctly with a UTF-8 application, a DataD=
irect driver manager, and psqlodbc you must do the following.



 - Use the header files (e.g. sql.h) from the DataDirect driver manage=
r installation.



 - Compile the driver with the SQLWCHARSHORT define (e.g. #define SQLW=
CHARSHORT=3D1).

    This will cause SQLWCHAR to be defined as an unsign=
ed short.



  - Compile the application with the SQLWCHARSHORT define (e.g. #=
define SQLWCHARSHORT=3D1).

    This will cause SQLWCHAR to be defined as an unsign=
ed short.



 - Set the DriverUnicodeType data source attribute to 1  (i.e. Dr=
iverUnicodeType=3D1 in your odbc.ini file).

   This tells the driver manager that your driver is expecti=
ng UTF-16 encoding for all SQLWCHAR

   parameters.



 - If your database encoding is UTF-8, the descriptor for character co=
lumns will be a wide type.

   For example, a char(1) column will be SQL_WCHAR.



- If you do something with a sql statement (e.g. Do a SQLExecDirectW(“=
;insert into T1 values (‘a’)),

  the driver manager (DataDirect) will convert the string from UT=
F-8 to UTF-16, pass it to the

  the driver as UTF-16. The driver will then translate it back to=
UTF-8. When the driver is

  unicode (i.e your compiled psqlodbc with as a unicode driver), =
then this translation

  in the driver manager happens weather or not you use a wide fun=
ction. The driver manager

  converts both SQLCHAR and SQLWCHAR parameters to UTF-16. It als=
o changes all calls to

  non-wide functions to calls to wide functions to the driver (e.=
g. SQLExecDirect gets changed

  to SQLExecDirectW). Note, SQLCHAR and SQLWCHAR strings are not =
translated from UTF-8

  to UTF-16 in the same way. If the call is to a non-wide functio=
n, then the translation assumes

  that the string contains only ASCII characters. A non-ASCII cha=
racter will probably get

  translated incorrectly. A call to wide functions translates UTF=
-8 to UTF-16 correctly.



 - When using parameters, you must be use the appropriate SQL and C ty=
pe when describing

    the parameters column values. If you say the C type=
is SQL_C_CHAR, the value is assumed

    to be in UTF-8 by the driver for a database encoded=
in UTF-8. IF you say the C type is

   SQL_C_WCHAR, I think the driver will assume the value is =
in UTF-16 and try to translate

   it to UTF-8.



 - When using column values, you must use the appropriate SQL C type. =
If you say the column C

   type is SQL_C_WCHAR, then the returned value will be tran=
slated to UTF-16 by the driver.

   If you say the column C type is SQL_C_CHAR, then the valu=
e will be returned as UTF-8.



.....Ken





On 5/5/09 11:13 PM, "Hiroshi Inoue" < p">inoue@tpf.co.jp> wrote:

>Ken Sell wrote:

> Hi,

>

> I wasn’t a member of the mailing list when I sent this, so IR=
17;m not sure

> it actually made it out there.

> I apologize if this is a duplicate.

>

> ....Ken

>

>

> On 5/4/09 11:49 AM, "Ken Sell" < m.com">ksell@greenplum.com> wrote:

>

>     Hi,

>

>     I’m a the new connectivity developer at =
GreenPlum. GreenPlum makes a

>     data warehouse DBMS based on PostgreSQL.

>     I’m working on a problem where a user is=
attempting to insert a

>     non-ASCII UTF-8 values (i.e. An umlaut over an=
‘o’).

>     The test does an insert via an SQLExecDirectW =
into a table.  The

>     text looks like this:

>

>       "insert into t1 values ('&oum=
l;')"

>

>     I’ve built and debugged the postgreSQL d=
river (version 8.02.0500).

>     It looks like the text makes it through the >
>     Driver Manager (i.e. DataDirect) ok. I see the=
correct value in

>     SQLExecDirectW in odbcapiw.c, but I

>     also see the code in SQLExecDirectW call ucs2_=
to_utf8. ucs2_to_utf8

>     tries to interpret the value as

>     UCS2, but the value is UTF-8. The value is cor=
rupted by ucs2_to_utf8.



Psqlodbc Unicode driver uses UTF-16 encoding and your application uses

UTF-8 encoding. Isn't the URL

/unicode6.html">http://media.datadirect.com/download/docs/od bc/allodbc/refe=
rence/unicode6.html

related to your problem ?



regards,

Hiroshi Inoue